using Microsoft.EntityFrameworkCore;
using System.Security.Cryptography;
using System.Text;
using WeatherSystem.API.Models;

namespace WeatherSystem.API.Data
{
    /// <summary>
    /// 天气系统数据库上下文
    /// </summary>
    public class WeatherDbContext : DbContext
    {
        public WeatherDbContext(DbContextOptions<WeatherDbContext> options) : base(options)
        {
        }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            base.OnConfiguring(optionsBuilder);
            // 忽略模型变化警告
            optionsBuilder.ConfigureWarnings(warnings => 
                warnings.Ignore(Microsoft.EntityFrameworkCore.Diagnostics.RelationalEventId.PendingModelChangesWarning));
        }

        // DbSet 属性
        public DbSet<WeatherDataEntry> WeatherDataEntries { get; set; }
        public DbSet<MetricValue> MetricValues { get; set; }
        public DbSet<MetricDefinition> MetricDefinitions { get; set; }
        public DbSet<StatisticsEntry> StatisticsEntries { get; set; }
        public DbSet<StatisticValue> StatisticValues { get; set; }
        public DbSet<City> Cities { get; set; }
        public DbSet<User> Users { get; set; }
        public DbSet<RefreshToken> RefreshTokens { get; set; }
        public DbSet<DataCollectionLog> DataCollectionLogs { get; set; }
        public DbSet<SystemConfiguration> SystemConfigurations { get; set; }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);

            // 配置实体关系
            ConfigureWeatherDataEntry(modelBuilder);
            ConfigureMetricValue(modelBuilder);
            ConfigureMetricDefinition(modelBuilder);
            ConfigureStatisticsEntry(modelBuilder);
            ConfigureStatisticValue(modelBuilder);
            ConfigureDataCollectionLogs(modelBuilder);
            ConfigureUsers(modelBuilder);
            ConfigureRefreshTokens(modelBuilder);
            ConfigureCities(modelBuilder);
            ConfigureSystemConfigurations(modelBuilder);

            // 种子数据
            SeedData(modelBuilder);
        }

        private void ConfigureWeatherDataEntry(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<WeatherDataEntry>(entity =>
            {
                entity.HasKey(e => e.Id);
                
                entity.HasOne(e => e.City)
                      .WithMany() // 不在City实体中定义导航属性，避免混淆
                      .HasForeignKey(e => e.CityId)
                      .OnDelete(DeleteBehavior.Restrict);

                entity.HasIndex(e => new { e.CityId, e.RecordedAt })
                      .HasDatabaseName("IX_WeatherDataEntry_CityId_RecordedAt");
                
                entity.HasIndex(e => e.RecordedAt)
                      .HasDatabaseName("IX_WeatherDataEntry_RecordedAt");
                
                // 查询优化复合索引
                entity.HasIndex(e => new { e.CityId, e.Source, e.RecordedAt })
                      .HasDatabaseName("IX_WeatherDataEntry_CityId_Source_RecordedAt");
                
                entity.HasIndex(e => new { e.Source, e.RecordedAt })
                      .HasDatabaseName("IX_WeatherDataEntry_Source_RecordedAt");
            });
        }

        private void ConfigureMetricValue(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<MetricValue>(entity =>
            {
                entity.HasKey(e => e.Id);
                
                entity.HasOne(e => e.WeatherDataEntry)
                      .WithMany(w => w.Metrics)
                      .HasForeignKey(e => e.WeatherDataEntryId)
                      .OnDelete(DeleteBehavior.Cascade);

                entity.HasOne(e => e.Definition)
                      .WithMany(d => d.Values)
                      .HasForeignKey(e => e.MetricKey)
                      .HasPrincipalKey(d => d.Key)
                      .OnDelete(DeleteBehavior.Restrict);

                // 关键性能索引
                entity.HasIndex(e => new { e.WeatherDataEntryId, e.MetricKey })
                      .HasDatabaseName("IX_MetricValue_EntryId_MetricKey");
                
                entity.HasIndex(e => new { e.MetricKey, e.RawValue })
                      .HasDatabaseName("IX_MetricValue_MetricKey_RawValue");
                
                // EAV查询优化复合索引
                entity.HasIndex(e => new { e.MetricKey, e.DataType, e.RawValue })
                      .HasDatabaseName("IX_MetricValue_MetricKey_DataType_RawValue");
                
                // 时间范围查询优化（通过WeatherDataEntry的时间字段）
                entity.HasIndex(e => new { e.WeatherDataEntryId, e.MetricKey, e.DataType })
                      .HasDatabaseName("IX_MetricValue_EntryId_MetricKey_DataType");
            });
        }

        private void ConfigureMetricDefinition(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<MetricDefinition>(entity =>
            {
                entity.HasKey(e => e.Id);
                
                entity.HasIndex(e => e.Key).IsUnique()
                      .HasDatabaseName("IX_MetricDefinition_Key");
                
                entity.HasIndex(e => new { e.IsActive, e.DisplayOrder })
                      .HasDatabaseName("IX_MetricDefinition_IsActive_DisplayOrder");
            });
        }


        private void ConfigureStatisticsEntry(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<StatisticsEntry>(entity =>
            {
                entity.HasKey(e => e.Id);
                
                entity.HasOne(e => e.City)
                      .WithMany()
                      .HasForeignKey(e => e.CityId)
                      .OnDelete(DeleteBehavior.Restrict);

                entity.HasIndex(e => new { e.CityId, e.StatisticType, e.StatisticDate })
                      .HasDatabaseName("IX_StatisticsEntry_CityId_StatisticType_StatisticDate");
                      
                entity.HasIndex(e => e.StatisticDate)
                      .HasDatabaseName("IX_StatisticsEntry_StatisticDate");
            });
        }

        private void ConfigureStatisticValue(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<StatisticValue>(entity =>
            {
                entity.HasKey(e => e.Id);
                
                entity.HasOne(e => e.StatisticsEntry)
                      .WithMany(s => s.Values)
                      .HasForeignKey(e => e.StatisticsEntryId)
                      .OnDelete(DeleteBehavior.Cascade);

                entity.HasOne(e => e.Definition)
                      .WithMany()
                      .HasForeignKey(e => e.MetricKey)
                      .HasPrincipalKey(d => d.Key)
                      .OnDelete(DeleteBehavior.Restrict);

                // 关键性能索引
                entity.HasIndex(e => new { e.StatisticsEntryId, e.MetricKey, e.AggregationType })
                      .HasDatabaseName("IX_StatisticValue_EntryId_MetricKey_AggregationType");
                      
                entity.HasIndex(e => new { e.MetricKey, e.AggregationType })
                      .HasDatabaseName("IX_StatisticValue_MetricKey_AggregationType");
            });
        }


        private void ConfigureDataCollectionLogs(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<DataCollectionLog>(entity =>
            {
                entity.HasKey(e => e.Id);
                
                entity.HasOne(e => e.City)
                      .WithMany()
                      .HasForeignKey(e => e.CityId)
                      .OnDelete(DeleteBehavior.Restrict)
                      .IsRequired(false); // 允许CityId为null

                entity.HasIndex(e => new { e.CityId, e.ExecutionTime })
                      .HasDatabaseName("IX_DataCollectionLogs_CityId_ExecutionTime");
            });
        }

        private void ConfigureUsers(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<User>(entity =>
            {
                entity.HasKey(e => e.Id);
                
                entity.HasIndex(e => e.Username).IsUnique();
                entity.HasIndex(e => e.Email).IsUnique();
            });
        }

        private void ConfigureCities(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<City>(entity =>
            {
                entity.HasKey(e => e.Id);
            });
        }

        private void ConfigureSystemConfigurations(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<SystemConfiguration>(entity =>
            {
                entity.HasKey(e => e.Id);
                
                entity.HasIndex(e => e.ConfigKey).IsUnique();
            });
        }

        private void SeedData(ModelBuilder modelBuilder)
        {
            // 种子指标定义数据
            SeedMetricDefinitions(modelBuilder);

            // 种子城市数据 - 使用固定的GUID值
            var wuhanCityId = new Guid("11111111-1111-1111-1111-111111111111");
            var shanghaiCityId = new Guid("22222222-2222-2222-2222-222222222222");
            var beijingCityId = new Guid("33333333-3333-3333-3333-333333333333");
            var guangzhouCityId = new Guid("44444444-4444-4444-4444-444444444444");
            var chengduCityId = new Guid("55555555-5555-5555-5555-555555555555");
            var xianCityId = new Guid("66666666-6666-6666-6666-666666666666");
            
            modelBuilder.Entity<City>().HasData(
                new City
                {
                    Id = wuhanCityId,
                    Name = "武汉",
                    Country = "中国",
                    Latitude = 30.5928m,
                    Longitude = 114.3055m,
                    TimeZone = "Asia/Shanghai",
                    LocationTechType = "GPS",
                    CoordinateSystem = "WGS84",
                    IsActive = true,
                    AutoCollectEnabled = true,
                    CreatedAt = new DateTime(2024, 1, 1, 0, 0, 0, DateTimeKind.Utc)
                },
                new City
                {
                    Id = shanghaiCityId,
                    Name = "上海",
                    Country = "中国",
                    Latitude = 31.2304m,
                    Longitude = 121.4737m,
                    TimeZone = "Asia/Shanghai",
                    LocationTechType = "GPS",
                    CoordinateSystem = "WGS84",
                    IsActive = true,
                    AutoCollectEnabled = true,
                    CreatedAt = new DateTime(2024, 1, 1, 0, 0, 0, DateTimeKind.Utc)
                },
                new City
                {
                    Id = beijingCityId,
                    Name = "北京",
                    Country = "中国",
                    Latitude = 39.9042m,
                    Longitude = 116.4074m,
                    TimeZone = "Asia/Shanghai",
                    LocationTechType = "GPS",
                    CoordinateSystem = "WGS84",
                    IsActive = true,
                    AutoCollectEnabled = true,
                    CreatedAt = new DateTime(2024, 1, 1, 0, 0, 0, DateTimeKind.Utc)
                },
                new City
                {
                    Id = guangzhouCityId,
                    Name = "广州",
                    Country = "中国",
                    Latitude = 23.1291m,
                    Longitude = 113.2644m,
                    TimeZone = "Asia/Shanghai",
                    LocationTechType = "GPS",
                    CoordinateSystem = "WGS84",
                    IsActive = true,
                    AutoCollectEnabled = true,
                    CreatedAt = new DateTime(2024, 1, 1, 0, 0, 0, DateTimeKind.Utc)
                },
                new City
                {
                    Id = chengduCityId,
                    Name = "成都",
                    Country = "中国",
                    Latitude = 30.5728m,
                    Longitude = 104.0668m,
                    TimeZone = "Asia/Shanghai",
                    LocationTechType = "GPS",
                    CoordinateSystem = "WGS84",
                    IsActive = true,
                    AutoCollectEnabled = true,
                    CreatedAt = new DateTime(2024, 1, 1, 0, 0, 0, DateTimeKind.Utc)
                },
                new City
                {
                    Id = xianCityId,
                    Name = "西安",
                    Country = "中国",
                    Latitude = 34.3416m,
                    Longitude = 108.9398m,
                    TimeZone = "Asia/Shanghai",
                    LocationTechType = "GPS",
                    CoordinateSystem = "WGS84",
                    IsActive = true,
                    AutoCollectEnabled = true,
                    CreatedAt = new DateTime(2024, 1, 1, 0, 0, 0, DateTimeKind.Utc)
                }
            );

            // 种子用户数据 - 默认管理员用户
            modelBuilder.Entity<User>().HasData(
                new User
                {
                    Id = new Guid("77777777-7777-7777-7777-777777777777"),
                    Username = "admin",
                    Email = "admin@weathersystem.com",
                    PasswordHash = "jGl25bVBBBW96Qi9Te4V37Fnqchz/Eu4qB9vKrRIqRg=", // admin123的哈希值
                    Role = UserRole.Admin,
                    IsActive = true,
                    CreatedAt = new DateTime(2024, 1, 1, 0, 0, 0, DateTimeKind.Utc)
                }
            );

            // 种子系统配置数据
            modelBuilder.Entity<SystemConfiguration>().HasData(
                new SystemConfiguration
                {
                    Id = new Guid("88888888-8888-8888-8888-888888888888"),
                    ConfigKey = "WeatherAPI_BaseUrl",
                    ConfigValue = "https://api.openweathermap.org/data/2.5",
                    Description = "天气API基础URL",
                    CreatedAt = new DateTime(2024, 1, 1, 0, 0, 0, DateTimeKind.Utc),
                    UpdatedAt = new DateTime(2024, 1, 1, 0, 0, 0, DateTimeKind.Utc)
                },
                new SystemConfiguration
                {
                    Id = new Guid("99999999-9999-9999-9999-999999999999"),
                    ConfigKey = "DataCollection_Interval",
                    ConfigValue = "300",
                    Description = "数据采集间隔(秒)",
                    CreatedAt = new DateTime(2024, 1, 1, 0, 0, 0, DateTimeKind.Utc),
                    UpdatedAt = new DateTime(2024, 1, 1, 0, 0, 0, DateTimeKind.Utc)
                },
                new SystemConfiguration
                {
                    Id = new Guid("aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"),
                    ConfigKey = "Statistics_GenerationTime",
                    ConfigValue = "02:00",
                    Description = "统计数据生成时间",
                    CreatedAt = new DateTime(2024, 1, 1, 0, 0, 0, DateTimeKind.Utc),
                    UpdatedAt = new DateTime(2024, 1, 1, 0, 0, 0, DateTimeKind.Utc)
                },
                new SystemConfiguration
                {
                    Id = new Guid("bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb"),
                    ConfigKey = "MaxRetryAttempts",
                    ConfigValue = "3",
                    Description = "最大重试次数",
                    CreatedAt = new DateTime(2024, 1, 1, 0, 0, 0, DateTimeKind.Utc),
                    UpdatedAt = new DateTime(2024, 1, 1, 0, 0, 0, DateTimeKind.Utc)
                }
            );
        }

        private void SeedMetricDefinitions(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<MetricDefinition>().HasData(
                new MetricDefinition
                {
                    Id = new Guid("aaaaaaaa-1111-1111-1111-111111111111"),
                    Key = "temperature",
                    Name = "温度",
                    Unit = "°C",
                    Description = "环境温度",
                    DataType = "decimal",
                    IsRequired = true,
                    IsDefault = true,
                    DisplayOrder = 1,
                    ValidationRules = "{\"MinValue\": -50, \"MaxValue\": 60}",
                    CreatedAt = new DateTime(2024, 1, 1, 0, 0, 0, DateTimeKind.Utc),
                    UpdatedAt = new DateTime(2024, 1, 1, 0, 0, 0, DateTimeKind.Utc)
                },
                new MetricDefinition
                {
                    Id = new Guid("aaaaaaaa-2222-2222-2222-222222222222"),
                    Key = "humidity",
                    Name = "湿度",
                    Unit = "%",
                    Description = "相对湿度",
                    DataType = "decimal",
                    IsRequired = true,
                    IsDefault = true,
                    DisplayOrder = 2,
                    ValidationRules = "{\"MinValue\": 0, \"MaxValue\": 100}",
                    CreatedAt = new DateTime(2024, 1, 1, 0, 0, 0, DateTimeKind.Utc),
                    UpdatedAt = new DateTime(2024, 1, 1, 0, 0, 0, DateTimeKind.Utc)
                },
                new MetricDefinition
                {
                    Id = new Guid("aaaaaaaa-3333-3333-3333-333333333333"),
                    Key = "pressure",
                    Name = "气压",
                    Unit = "hPa",
                    Description = "大气压强",
                    DataType = "decimal",
                    IsRequired = true,
                    IsDefault = true,
                    DisplayOrder = 3,
                    ValidationRules = "{\"MinValue\": 900, \"MaxValue\": 1100}",
                    CreatedAt = new DateTime(2024, 1, 1, 0, 0, 0, DateTimeKind.Utc),
                    UpdatedAt = new DateTime(2024, 1, 1, 0, 0, 0, DateTimeKind.Utc)
                },
                new MetricDefinition
                {
                    Id = new Guid("aaaaaaaa-4444-4444-4444-444444444444"),
                    Key = "windSpeed",
                    Name = "风速",
                    Unit = "m/s",
                    Description = "风速",
                    DataType = "decimal",
                    IsRequired = true,
                    IsDefault = true,
                    DisplayOrder = 4,
                    ValidationRules = "{\"MinValue\": 0, \"MaxValue\": 100}",
                    CreatedAt = new DateTime(2024, 1, 1, 0, 0, 0, DateTimeKind.Utc),
                    UpdatedAt = new DateTime(2024, 1, 1, 0, 0, 0, DateTimeKind.Utc)
                },
                new MetricDefinition
                {
                    Id = new Guid("aaaaaaaa-5555-5555-5555-555555555555"),
                    Key = "windDirection",
                    Name = "风向",
                    Unit = "°",
                    Description = "风向角度",
                    DataType = "decimal",
                    IsRequired = false,
                    IsDefault = false,
                    DisplayOrder = 5,
                    ValidationRules = "{\"MinValue\": 0, \"MaxValue\": 360}",
                    CreatedAt = new DateTime(2024, 1, 1, 0, 0, 0, DateTimeKind.Utc),
                    UpdatedAt = new DateTime(2024, 1, 1, 0, 0, 0, DateTimeKind.Utc)
                },
                new MetricDefinition
                {
                    Id = new Guid("aaaaaaaa-6666-6666-6666-666666666666"),
                    Key = "visibility",
                    Name = "能见度",
                    Unit = "km",
                    Description = "大气能见度",
                    DataType = "decimal",
                    IsRequired = false,
                    IsDefault = false,
                    DisplayOrder = 6,
                    ValidationRules = "{\"MinValue\": 0, \"MaxValue\": 50}",
                    CreatedAt = new DateTime(2024, 1, 1, 0, 0, 0, DateTimeKind.Utc),
                    UpdatedAt = new DateTime(2024, 1, 1, 0, 0, 0, DateTimeKind.Utc)
                },
                new MetricDefinition
                {
                    Id = new Guid("aaaaaaaa-7777-7777-7777-777777777777"),
                    Key = "uvIndex",
                    Name = "紫外线指数",
                    Unit = "",
                    Description = "紫外线强度指数",
                    DataType = "decimal",
                    IsRequired = false,
                    IsDefault = false,
                    DisplayOrder = 7,
                    ValidationRules = "{\"MinValue\": 0, \"MaxValue\": 15}",
                    CreatedAt = new DateTime(2024, 1, 1, 0, 0, 0, DateTimeKind.Utc),
                    UpdatedAt = new DateTime(2024, 1, 1, 0, 0, 0, DateTimeKind.Utc)
                },
                new MetricDefinition
                {
                    Id = new Guid("aaaaaaaa-8888-8888-8888-888888888888"),
                    Key = "weatherCondition",
                    Name = "天气状况",
                    Unit = "",
                    Description = "天气描述",
                    DataType = "string",
                    IsRequired = false,
                    IsDefault = true,
                    DisplayOrder = 8,
                    ValidationRules = "{\"MaxLength\": 100}",
                    CreatedAt = new DateTime(2024, 1, 1, 0, 0, 0, DateTimeKind.Utc),
                    UpdatedAt = new DateTime(2024, 1, 1, 0, 0, 0, DateTimeKind.Utc)
                }
            );
        }

        /// <summary>
        /// 配置RefreshToken实体
        /// </summary>
        private void ConfigureRefreshTokens(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<RefreshToken>(entity =>
            {
                entity.HasKey(r => r.Id);

                entity.Property(r => r.Token)
                    .IsRequired()
                    .HasMaxLength(255);

                entity.Property(r => r.CreatedByIp)
                    .HasMaxLength(100);

                entity.Property(r => r.RevokedByIp)
                    .HasMaxLength(100);

                // 外键关系
                entity.HasOne(r => r.User)
                    .WithMany(u => u.RefreshTokens)
                    .HasForeignKey(r => r.UserId)
                    .OnDelete(DeleteBehavior.Cascade);

                // 索引
                entity.HasIndex(r => r.Token).IsUnique();
                entity.HasIndex(r => new { r.UserId, r.ExpiresAt });
                entity.HasIndex(r => r.ExpiresAt);
            });
        }

        /// <summary>
        /// 哈希密码 - 与AuthController保持一致
        /// </summary>
        private string HashPassword(string password)
        {
            using var sha256 = SHA256.Create();
            var hashedBytes = sha256.ComputeHash(Encoding.UTF8.GetBytes(password + "WeatherSystemSalt"));
            return Convert.ToBase64String(hashedBytes);
        }
    }
}